
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/11/2013 19:59:53
-- Generated from EDMX file: C:\Haim\LybrixNursingCommon\NursingDB.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [TestDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Teekcustomer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Teeks] DROP CONSTRAINT [FK_Teekcustomer];
GO
IF OBJECT_ID(N'[dbo].[FK_Teekagent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Teeks] DROP CONSTRAINT [FK_Teekagent];
GO
IF OBJECT_ID(N'[dbo].[FK_TeekSapak]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Teeks] DROP CONSTRAINT [FK_TeekSapak];
GO
IF OBJECT_ID(N'[dbo].[FK_TeekTeekTransactionLog_Teek]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TeekTeekTransactionLog] DROP CONSTRAINT [FK_TeekTeekTransactionLog_Teek];
GO
IF OBJECT_ID(N'[dbo].[FK_TeekTeekTransactionLog_TeekTransactionLog]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TeekTeekTransactionLog] DROP CONSTRAINT [FK_TeekTeekTransactionLog_TeekTransactionLog];
GO
IF OBJECT_ID(N'[dbo].[FK_TeekTeekDocs]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TeekDocs] DROP CONSTRAINT [FK_TeekTeekDocs];
GO
IF OBJECT_ID(N'[dbo].[FK_TeekSapakPayments]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SapakPayments] DROP CONSTRAINT [FK_TeekSapakPayments];
GO
IF OBJECT_ID(N'[dbo].[FK_TeekTeekContacts]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TeekContacts] DROP CONSTRAINT [FK_TeekTeekContacts];
GO
IF OBJECT_ID(N'[dbo].[FK_TeekNursingHome]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Teeks] DROP CONSTRAINT [FK_TeekNursingHome];
GO
IF OBJECT_ID(N'[dbo].[FK_PaymentHeaderTeekPayments]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TeekPayments] DROP CONSTRAINT [FK_PaymentHeaderTeekPayments];
GO
IF OBJECT_ID(N'[dbo].[FK_TeekPaymentHeader]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PaymentHeaders] DROP CONSTRAINT [FK_TeekPaymentHeader];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[agents]', 'U') IS NOT NULL
    DROP TABLE [dbo].[agents];
GO
IF OBJECT_ID(N'[dbo].[customers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[customers];
GO
IF OBJECT_ID(N'[dbo].[Sapaks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Sapaks];
GO
IF OBJECT_ID(N'[dbo].[Teeks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Teeks];
GO
IF OBJECT_ID(N'[dbo].[TeekTransactionLogs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TeekTransactionLogs];
GO
IF OBJECT_ID(N'[dbo].[formulae]', 'U') IS NOT NULL
    DROP TABLE [dbo].[formulae];
GO
IF OBJECT_ID(N'[dbo].[NursingHomes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[NursingHomes];
GO
IF OBJECT_ID(N'[dbo].[TeekDocs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TeekDocs];
GO
IF OBJECT_ID(N'[dbo].[TeekContacts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TeekContacts];
GO
IF OBJECT_ID(N'[dbo].[SapakPayments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SapakPayments];
GO
IF OBJECT_ID(N'[dbo].[TeekPayments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TeekPayments];
GO
IF OBJECT_ID(N'[dbo].[PaymentHeaders]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PaymentHeaders];
GO
IF OBJECT_ID(N'[dbo].[TeekTeekTransactionLog]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TeekTeekTransactionLog];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'agents'
CREATE TABLE [dbo].[agents] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [firstName] nvarchar(max)  NOT NULL,
    [lastName] nvarchar(max)  NOT NULL,
    [socialId] nvarchar(max)  NULL,
    [password] nvarchar(max)  NULL,
    [question] nvarchar(max)  NULL,
    [answer] nvarchar(max)  NULL,
    [phone1] nvarchar(max)  NOT NULL,
    [phone2] nvarchar(max)  NULL,
    [region] nvarchar(max)  NULL,
    [userName] nvarchar(max)  NULL,
    [agentID] nvarchar(max)  NOT NULL,
    [isAdmin] bit  NULL,
    [NursingHomeId] int  NOT NULL,
    [isAgent] bit  NULL,
    [isSuperUser] bit  NULL
);
GO

-- Creating table 'customers'
CREATE TABLE [dbo].[customers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [teekOpen_date] datetime  NOT NULL,
    [custNumber] nvarchar(max)  NOT NULL,
    [firstName] nvarchar(max)  NOT NULL,
    [lastName] nvarchar(max)  NOT NULL,
    [socialID] nvarchar(max)  NOT NULL,
    [numOfChildren] smallint  NULL,
    [age] smallint  NULL,
    [familyStatus] nvarchar(max)  NOT NULL,
    [status] nvarchar(max)  NOT NULL,
    [pass_date] datetime  NULL,
    [location] nvarchar(max)  NOT NULL,
    [livesAlone] bit  NOT NULL,
    [livesWithfamily] bit  NOT NULL,
    [livesWithPartner] bit  NOT NULL,
    [hasAssistance] bit  NOT NULL,
    [hasForignAssistance] bit  NOT NULL,
    [isBayetMotam] bit  NOT NULL,
    [hasStairs] bit  NOT NULL,
    [hasElevator] bit  NOT NULL,
    [status_descr] nvarchar(max)  NULL,
    [familyStatus_descr] nvarchar(max)  NULL,
    [location_descr] nvarchar(max)  NULL,
    [cameFrom] nvarchar(max)  NULL
);
GO

-- Creating table 'Sapaks'
CREATE TABLE [dbo].[Sapaks] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [sapakID] nvarchar(max)  NOT NULL,
    [sapakName] nvarchar(max)  NOT NULL,
    [sapakType] int  NOT NULL,
    [percentage] float  NULL,
    [Amount] float  NULL,
    [userName] nvarchar(max)  NULL,
    [password] nvarchar(max)  NULL,
    [startWork] datetime  NULL,
    [email] nvarchar(max)  NULL,
    [phone] nvarchar(max)  NULL,
    [mobile] nvarchar(max)  NULL,
    [fax] nvarchar(max)  NULL,
    [area] nvarchar(max)  NULL,
    [methodType] int  NULL
);
GO

-- Creating table 'Teeks'
CREATE TABLE [dbo].[Teeks] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [status] nvarchar(max)  NOT NULL,
    [follow_date] datetime  NULL,
    [isClosed] nvarchar(max)  NULL,
    [closeDate] datetime  NULL,
    [isAccepted] nvarchar(max)  NULL,
    [AcceptanceDate] datetime  NULL,
    [additionalInfo] nvarchar(max)  NULL,
    [sendEmail] bit  NULL,
    [sendSMS] bit  NULL,
    [status_descr] nvarchar(max)  NULL,
    [Teekstatus_descr] nvarchar(max)  NULL,
    [Teekstatus] nvarchar(max)  NULL,
    [customer_Id] int  NOT NULL,
    [agent_Id] int  NULL,
    [Sapak_Id] int  NULL,
    [NursingHome_Id] int  NULL
);
GO

-- Creating table 'TeekTransactionLogs'
CREATE TABLE [dbo].[TeekTransactionLogs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [update_dt] datetime  NOT NULL,
    [action] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'formulae'
CREATE TABLE [dbo].[formulae] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [bo] nvarchar(max)  NOT NULL,
    [lastvalue] int  NOT NULL
);
GO

-- Creating table 'NursingHomes'
CREATE TABLE [dbo].[NursingHomes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [HomeName] nvarchar(max)  NOT NULL,
    [HomeID] nvarchar(max)  NOT NULL,
    [HomeContact] nvarchar(max)  NULL,
    [meContactFax] nvarchar(max)  NULL,
    [HomeContactPhone] nvarchar(max)  NULL,
    [HomeContactMobile] nvarchar(max)  NOT NULL,
    [HomeAddress] nvarchar(max)  NULL
);
GO

-- Creating table 'TeekDocs'
CREATE TABLE [dbo].[TeekDocs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [docName] nvarchar(max)  NOT NULL,
    [docPath] nvarchar(max)  NOT NULL,
    [docType] nvarchar(max)  NULL,
    [createdBy] nvarchar(max)  NULL,
    [update_dt] datetime  NOT NULL,
    [creation_dt] datetime  NOT NULL,
    [TeekId] int  NOT NULL
);
GO

-- Creating table 'TeekContacts'
CREATE TABLE [dbo].[TeekContacts] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [contactName] nvarchar(max)  NULL,
    [is_primary] bit  NULL,
    [is_family] bit  NULL,
    [is_apotrobs] bit  NULL,
    [address] nvarchar(max)  NULL,
    [phone] nvarchar(max)  NULL,
    [mobile] nvarchar(max)  NULL,
    [TeekId] int  NOT NULL
);
GO

-- Creating table 'SapakPayments'
CREATE TABLE [dbo].[SapakPayments] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [payment] float  NOT NULL,
    [payment_dt] datetime  NOT NULL,
    [TeekId] int  NOT NULL
);
GO

-- Creating table 'TeekPayments'
CREATE TABLE [dbo].[TeekPayments] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [paymentsNum] int  NULL,
    [PaymentDate] datetime  NOT NULL,
    [PaymentAmount] float  NOT NULL,
    [PaymentType] nvarchar(max)  NOT NULL,
    [PaymentType_descr] nvarchar(max)  NOT NULL,
    [PaymentMethod] nvarchar(max)  NOT NULL,
    [PaymentMethodId] nvarchar(max)  NOT NULL,
    [PaymentStatusId] nvarchar(max)  NOT NULL,
    [PaymentStatus_descr] nvarchar(max)  NOT NULL,
    [PaymentHeaderId] int  NOT NULL
);
GO

-- Creating table 'PaymentHeaders'
CREATE TABLE [dbo].[PaymentHeaders] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Source] nvarchar(max)  NOT NULL,
    [TotalAmount] nvarchar(max)  NOT NULL,
    [StatusId] nvarchar(max)  NOT NULL,
    [Status_descr] nvarchar(max)  NOT NULL,
    [Active] bit  NOT NULL,
    [Teek_Id] int  NOT NULL
);
GO

-- Creating table 'TeekTeekTransactionLog'
CREATE TABLE [dbo].[TeekTeekTransactionLog] (
    [Teeks_Id] int  NOT NULL,
    [TeekTransactionLogs_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'agents'
ALTER TABLE [dbo].[agents]
ADD CONSTRAINT [PK_agents]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'customers'
ALTER TABLE [dbo].[customers]
ADD CONSTRAINT [PK_customers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Sapaks'
ALTER TABLE [dbo].[Sapaks]
ADD CONSTRAINT [PK_Sapaks]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Teeks'
ALTER TABLE [dbo].[Teeks]
ADD CONSTRAINT [PK_Teeks]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TeekTransactionLogs'
ALTER TABLE [dbo].[TeekTransactionLogs]
ADD CONSTRAINT [PK_TeekTransactionLogs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'formulae'
ALTER TABLE [dbo].[formulae]
ADD CONSTRAINT [PK_formulae]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'NursingHomes'
ALTER TABLE [dbo].[NursingHomes]
ADD CONSTRAINT [PK_NursingHomes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TeekDocs'
ALTER TABLE [dbo].[TeekDocs]
ADD CONSTRAINT [PK_TeekDocs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TeekContacts'
ALTER TABLE [dbo].[TeekContacts]
ADD CONSTRAINT [PK_TeekContacts]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'SapakPayments'
ALTER TABLE [dbo].[SapakPayments]
ADD CONSTRAINT [PK_SapakPayments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TeekPayments'
ALTER TABLE [dbo].[TeekPayments]
ADD CONSTRAINT [PK_TeekPayments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PaymentHeaders'
ALTER TABLE [dbo].[PaymentHeaders]
ADD CONSTRAINT [PK_PaymentHeaders]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Teeks_Id], [TeekTransactionLogs_Id] in table 'TeekTeekTransactionLog'
ALTER TABLE [dbo].[TeekTeekTransactionLog]
ADD CONSTRAINT [PK_TeekTeekTransactionLog]
    PRIMARY KEY NONCLUSTERED ([Teeks_Id], [TeekTransactionLogs_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [customer_Id] in table 'Teeks'
ALTER TABLE [dbo].[Teeks]
ADD CONSTRAINT [FK_Teekcustomer]
    FOREIGN KEY ([customer_Id])
    REFERENCES [dbo].[customers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Teekcustomer'
CREATE INDEX [IX_FK_Teekcustomer]
ON [dbo].[Teeks]
    ([customer_Id]);
GO

-- Creating foreign key on [agent_Id] in table 'Teeks'
ALTER TABLE [dbo].[Teeks]
ADD CONSTRAINT [FK_Teekagent]
    FOREIGN KEY ([agent_Id])
    REFERENCES [dbo].[agents]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Teekagent'
CREATE INDEX [IX_FK_Teekagent]
ON [dbo].[Teeks]
    ([agent_Id]);
GO

-- Creating foreign key on [Sapak_Id] in table 'Teeks'
ALTER TABLE [dbo].[Teeks]
ADD CONSTRAINT [FK_TeekSapak]
    FOREIGN KEY ([Sapak_Id])
    REFERENCES [dbo].[Sapaks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeekSapak'
CREATE INDEX [IX_FK_TeekSapak]
ON [dbo].[Teeks]
    ([Sapak_Id]);
GO

-- Creating foreign key on [Teeks_Id] in table 'TeekTeekTransactionLog'
ALTER TABLE [dbo].[TeekTeekTransactionLog]
ADD CONSTRAINT [FK_TeekTeekTransactionLog_Teek]
    FOREIGN KEY ([Teeks_Id])
    REFERENCES [dbo].[Teeks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [TeekTransactionLogs_Id] in table 'TeekTeekTransactionLog'
ALTER TABLE [dbo].[TeekTeekTransactionLog]
ADD CONSTRAINT [FK_TeekTeekTransactionLog_TeekTransactionLog]
    FOREIGN KEY ([TeekTransactionLogs_Id])
    REFERENCES [dbo].[TeekTransactionLogs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeekTeekTransactionLog_TeekTransactionLog'
CREATE INDEX [IX_FK_TeekTeekTransactionLog_TeekTransactionLog]
ON [dbo].[TeekTeekTransactionLog]
    ([TeekTransactionLogs_Id]);
GO

-- Creating foreign key on [TeekId] in table 'TeekDocs'
ALTER TABLE [dbo].[TeekDocs]
ADD CONSTRAINT [FK_TeekTeekDocs]
    FOREIGN KEY ([TeekId])
    REFERENCES [dbo].[Teeks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeekTeekDocs'
CREATE INDEX [IX_FK_TeekTeekDocs]
ON [dbo].[TeekDocs]
    ([TeekId]);
GO

-- Creating foreign key on [TeekId] in table 'SapakPayments'
ALTER TABLE [dbo].[SapakPayments]
ADD CONSTRAINT [FK_TeekSapakPayments]
    FOREIGN KEY ([TeekId])
    REFERENCES [dbo].[Teeks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeekSapakPayments'
CREATE INDEX [IX_FK_TeekSapakPayments]
ON [dbo].[SapakPayments]
    ([TeekId]);
GO

-- Creating foreign key on [TeekId] in table 'TeekContacts'
ALTER TABLE [dbo].[TeekContacts]
ADD CONSTRAINT [FK_TeekTeekContacts]
    FOREIGN KEY ([TeekId])
    REFERENCES [dbo].[Teeks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeekTeekContacts'
CREATE INDEX [IX_FK_TeekTeekContacts]
ON [dbo].[TeekContacts]
    ([TeekId]);
GO

-- Creating foreign key on [NursingHome_Id] in table 'Teeks'
ALTER TABLE [dbo].[Teeks]
ADD CONSTRAINT [FK_TeekNursingHome]
    FOREIGN KEY ([NursingHome_Id])
    REFERENCES [dbo].[NursingHomes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeekNursingHome'
CREATE INDEX [IX_FK_TeekNursingHome]
ON [dbo].[Teeks]
    ([NursingHome_Id]);
GO

-- Creating foreign key on [PaymentHeaderId] in table 'TeekPayments'
ALTER TABLE [dbo].[TeekPayments]
ADD CONSTRAINT [FK_PaymentHeaderTeekPayments]
    FOREIGN KEY ([PaymentHeaderId])
    REFERENCES [dbo].[PaymentHeaders]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PaymentHeaderTeekPayments'
CREATE INDEX [IX_FK_PaymentHeaderTeekPayments]
ON [dbo].[TeekPayments]
    ([PaymentHeaderId]);
GO

-- Creating foreign key on [Teek_Id] in table 'PaymentHeaders'
ALTER TABLE [dbo].[PaymentHeaders]
ADD CONSTRAINT [FK_TeekPaymentHeader]
    FOREIGN KEY ([Teek_Id])
    REFERENCES [dbo].[Teeks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeekPaymentHeader'
CREATE INDEX [IX_FK_TeekPaymentHeader]
ON [dbo].[PaymentHeaders]
    ([Teek_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------